from datetime import datetime
import time
import pandas as pd
from db_conn import pd_conn_zfw, pd_conn_wfw


# 两类工单的工单类型
# order_1  1：普通任务-抢修 8:客需反馈、洁净问题  10:改造  11:整改  12:客诉  13:排查工单  14:C端差评工单
order_1 = '(1, 8, 10, 11, 12, 13, 14)'
# order_2  5:维护工单  6:保养工单
order_2 = '(5, 6)'


def work_order_1(startTime, endTime):
    # 1：普通任务-抢修 8:客需反馈、洁净问题  10:改造  11:整改  12:客诉  13:排查工单  14:C端差评工单
    sql1 = """
        select 
            f.f_id '站点ID', f.f_province '省', f.f_city '市', f.f_name '站点名称', f.f_work_start_time '开业时间'
            , case when f.f_station_num in (1,2,3,4,5,6,6001,6002) then '愿景系列'
                         when f.f_station_num in (6003,6004) then 'LITE版'
                         when f.f_station_num = 10 then '阿尔法'
                         else '其他' end as '机器类型'
            , t.f_id '工单ID'
            , if(t.f_type = 1, '抢修'
                , if(t.f_type = 14, 'C端差评工单'
                    , if(t.f_type = 13, '排查工单'
                        , if(t.f_type = 12, '客诉'
                            , if(t.f_type = 11, '整改'
                                , if(t.f_type = 10, '改造'
                                    , if(t.f_type = 8, '客需反馈、清洁问题'
                                        , if(t.f_type = 5 and f_label = '日常维护', '运维-日保养'
                                            , if(t.f_type = 5 and f_label = '月度维护', '运维-月保养'
                                                , if(t.f_type = 6 and f_label = '大保养', '大保养'
                                                    , if(t.f_type = 6 and f_label = '小保养', '小保养'
                                                        , t.f_type ))) ))) ))) )) '工单类型'
            , t.f_user_names '执行人员'
            , t.f_label '工单预警'
            , t.f_content '工单描述'
            , t.f_create_user_name '创建人员'
            , t.f_create_time '创建时间'
            , t.f_complete_time '完成时间'
            , r6.f_user_name '完成人员'
            , r3.f_create_time '重启时间'					-- 第一次重启时间
            , r3.f_content '重启人员'
            , t.f_start_time '开始服务时间'
            , r4.f_create_time '响应时间'			-- 第一次开始服务时间
            , t.f_end_time '服务截止时间'
            , r1.f_create_time '跟进时间' 					  -- 最后一次跟进时间
            , r1.f_content '跟进记录'  -- 所有跟进记录
            , r2.f_create_time '留言时间'  				  -- 最后一次留言时间
            , r2.f_content '留言记录'  -- 所有留言记录
            , r5.f_create_time '申请延期时间'        -- 第一次申请时间
            , r5.f_content '延期记录'	
            , t.f_tag_names '标签内容'
            , if(r4.f_create_time is null, null, TIMESTAMPDIFF(minute, t.f_create_time, r4.f_create_time) / 60) '响应耗时h'
            , if(t.f_complete_time is null, null, TIMESTAMPDIFF(minute, t.f_create_time, t.f_complete_time) / 60) '完结耗时h'
            , if(t.f_complete_time is null, TIMESTAMPDIFF(minute, ifnull(ifnull(r1.last_create_time, r4.f_create_time), t.f_create_time), now()) / 60, null) '停留时长h' -- 最后一次跟进/响应/创建
            , t1.f_create_time '上次创建时间'
            , t1.timeDiff '工单间隔h'
            , if(r4.f_create_time is null, '新建'
                , if(r4.f_create_time is not null and t.f_complete_time is null and t.f_end_time >= now(), '进行中' 
                    , if(r4.f_create_time is not null and t.f_complete_time is null and t.f_end_time < now(), '逾期未完结'
                        , if(t.f_complete_time is not null and t.f_end_time >= t.f_complete_time, '按时完结'
                            , if(t.f_complete_time is not null and t.f_end_time < t.f_complete_time, '逾期完结', 'other'))))) '工单状态'
            , t.f_score '工单评分'
            , if(t.f_type = 1, t.f_give_amt, null) '绩效金额（抢修）'
            # , e.f_water_meter_old '上期用水量'
            # , e.f_water_current_dosage '本期用水量'
            # , e.f_electricity_meter_old '上期用电量'
            # , e.f_electricity_current_dosage '本期用电量'
        from t_factory f
        left join t_task t on t.f_factoryIds = f.f_id
        left join (
            select f_task_id, min(f_create_time) f_create_time, substring(f_content, 1, CHAR_LENGTH(f_content) - 4) f_content 
            from t_task_record where f_type = 3 and f_content like '%重启任务%' 
            group by f_task_id
        ) r3 on t.f_id = r3.f_task_id
        left join (
            select f_task_id, min(f_create_time) f_create_time 
            from t_task_record where f_content = '开始服务' 
            group by f_task_id
        ) r4 on t.f_id = r4.f_task_id
        left join (
            select f_task_id, max(f_create_time) f_create_time, max(f_create_time) last_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_type = 1 group by f_task_id
        ) r1 on t.f_id = r1.f_task_id
        left join (
            select f_task_id, max(f_create_time) f_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_type = 2 group by f_task_id
        ) r2 on t.f_id = r2.f_task_id
        left join (
            select f_task_id, min(f_create_time) f_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_content like '%申请%延期%' group by f_task_id
        ) r5 on t.f_id = r5.f_task_id
        left join (
            select f_task_id, f_user_name from t_task_record where f_type = 3 and f_content like '%完成任务%' 
            group by f_task_id
        ) r6 on t.f_id = r6.f_task_id
        left join (
            select B.f_task_id, B.f_id, B.f_type, A.f_create_time, TIMESTAMPDIFF(minute,A.f_create_time,B.f_create_time) / 60 timeDiff
            from (
                select a.*, (@i := @i + 1) as idx from (
                        select t.f_id f_task_id, f.f_id, t.f_type, t.f_create_time 
                        from t_factory f, t_task t 
                        where f.f_id = t.f_factoryIds and f.f_status = 0
                        and t.f_type in {_order_num} and t.f_create_time >= '{_startTime}' and t.f_create_time < '{_endTime}'
                        order by f.f_id, t.f_type, t.f_create_time
                ) as a , (select @i := 1) b
            ) A join (
                select a.*, (@j := @j + 1) as idx from (
                        select t.f_id f_task_id, f.f_id, t.f_type, t.f_create_time 
                        from t_factory f, t_task t 
                        where f.f_id = t.f_factoryIds and f.f_status = 0
                        and t.f_type in {_order_num} and t.f_create_time >= '{_startTime}' and t.f_create_time < '{_endTime}'
                        order by f.f_id, t.f_type, t.f_create_time
                ) as a , (select @j := 0) c
            ) B on A.idx = B.idx and A.f_id = B.f_id and A.f_type = B.f_type
        ) t1 on t.f_id = t1.f_task_id
        # left join t_task_water_electricity e on e.f_task_id = t.f_id

        where 
            t.f_type in {_order_num}
            and t.f_create_time >= '{_startTime}'
            and t.f_create_time < '{_endTime}'
            and t.f_del_status != 1
            and f.f_station_num in (6003,6004)
            and t.f_type in (1,13)
        order by f.f_id,t.f_type,t.f_create_time
    """.format(_startTime=startTime, _endTime=endTime, _order_num=order_1)

    # 配件-微服务
    sql3 = """
        select f_task_id, group_concat(f_part_name) f_part_name 
        from t_stock_record 
        where f_is_deleted = 0 and f_task_id is not null
        group by f_task_id
    """

    df1 = pd_conn_zfw(sql1)

    db = 'ygl_stock'
    df3 = pd_conn_wfw(sql3, db)

    df = df1.merge(df3, left_on='工单ID', right_on='f_task_id', how='left')

    col_nam = df.columns.tolist()
    col_nam.insert(25, '配件')
    df = df.reindex(columns=col_nam)
    df.loc[:, '配件'] = df.loc[:, 'f_part_name']
    df.drop(['f_task_id', 'f_part_name'], axis=1, inplace=True)

    return df


def work_order_2(startTime, endTime):

    # 5:维护工单  6:保养工单
    sql2 = """
        select 
            f.f_id '站点ID', f.f_province '省', f.f_city '市', f.f_name '站点名称', f.f_work_start_time '开业时间'
            , case when f.f_station_num in (1,2,3,4,5,6,6001,6002) then '愿景系列'
                         when f.f_station_num in (6003,6004) then 'LITE版'
                         when f.f_station_num = 10 then '阿尔法'
                         else '其他' end as '机器类型'
            , t.f_id '工单ID'
            , if(t.f_type = 1, '抢修'
                , if(t.f_type = 14, 'C端差评工单'
                    , if(t.f_type = 13, '排查工单'
                        , if(t.f_type = 12, '客诉'
                            , if(t.f_type = 11, '整改'
                                , if(t.f_type = 10, '改造'
                                    , if(t.f_type = 8, '客需反馈、清洁问题'
                                        , if(t.f_type = 5 and f_label = '日常维护', '运维-日保养'
                                            , if(t.f_type = 5 and f_label = '月度维护', '运维-月保养'
                                                , if(t.f_type = 6 and f_label = '大保养', '大保养'
                                                    , if(t.f_type = 6 and f_label = '小保养', '小保养'
                                                        , t.f_type ))) ))) ))) )) '工单类型'
            , t.f_user_names '执行人员'
            # , t.f_label '工单预警'
            # , t.f_content '工单描述'
            , t.f_create_user_name '创建人员'
            , t.f_create_time '创建时间'
            , t.f_complete_time '完成时间'
            , r6.f_user_name '完成人员'
            , r3.f_create_time '重启时间'					-- 第一次重启时间
            , r3.f_content '重启人员'
            , t.f_start_time '开始服务时间'
            , r4.f_create_time '响应时间'			-- 第一次开始服务时间
            , t.f_end_time '服务截止时间'
            , r1.f_create_time '跟进时间' 					  -- 最后一次跟进时间
            , r1.f_content '跟进记录'  -- 所有跟进记录
            , r2.f_create_time '留言时间'  				  -- 最后一次留言时间
            , r2.f_content '留言记录'  -- 所有留言记录
            , r5.f_create_time '申请延期时间'                 -- 第一次申请延期时间
            , r5.f_content '延期记录'	
            # , t.f_tag_names '标签内容'
            , if(r4.f_create_time is null, null, TIMESTAMPDIFF(minute, t.f_create_time, r4.f_create_time) / 60) '响应耗时h'
            , if(t.f_complete_time is null, null, TIMESTAMPDIFF(minute, t.f_create_time, t.f_complete_time) / 60) '完结耗时h'
            , if(t.f_complete_time is null, TIMESTAMPDIFF(minute, ifnull(ifnull(r1.last_create_time, r4.f_create_time), t.f_create_time), now()) / 60, null) '停留时长h' -- 最后一次跟进/响应/创建
            , t1.f_create_time '上次创建时间'
            , t1.timeDiff '工单间隔h'
            , if(r4.f_create_time is null, '新建'
                , if(r4.f_create_time is not null and t.f_complete_time is null and t.f_end_time >= now(), '进行中' 
                    , if(r4.f_create_time is not null and t.f_complete_time is null and t.f_end_time < now(), '逾期未完结'
                        , if(t.f_complete_time is not null and t.f_end_time >= t.f_complete_time, '按时完结'
                            , if(t.f_complete_time is not null and t.f_end_time < t.f_complete_time, '逾期完结', 'other'))))) '工单状态'
            , t.f_score '工单评分'
            # , if(t.f_type in (1, 10, 11, 13), t.f_give_amt, null) '绩效金额'
            , e.f_water_meter_old '上期用水量'
            , e.f_water_current_dosage '本期用水量'
            , e.f_electricity_meter_old '上期用电量'
            , e.f_electricity_current_dosage '本期用电量'
        from t_factory f
        left join t_task t on t.f_factoryIds = f.f_id
        left join (
            select f_task_id, min(f_create_time) f_create_time, substring(f_content, 1, CHAR_LENGTH(f_content) - 4) f_content 
            from t_task_record where f_type = 3 and f_content like '%重启任务%' 
            group by f_task_id
        ) r3 on t.f_id = r3.f_task_id
        left join (
            select f_task_id, min(f_create_time) f_create_time 
            from t_task_record where f_content = '开始服务' 
            group by f_task_id
        ) r4 on t.f_id = r4.f_task_id
        left join (
            select f_task_id, max(f_create_time) f_create_time, max(f_create_time) last_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_type = 1 group by f_task_id
        ) r1 on t.f_id = r1.f_task_id
        left join (
            select f_task_id, max(f_create_time) f_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_type = 2 group by f_task_id
        ) r2 on t.f_id = r2.f_task_id
        left join (
            select f_task_id, min(f_create_time) f_create_time, GROUP_CONCAT(f_create_time, f_content,'//') f_content 
            from t_task_record where f_content like '%申请%延期%' group by f_task_id
        ) r5 on t.f_id = r5.f_task_id
        left join (
            select f_task_id, f_user_name from t_task_record where f_type = 3 and f_content like '%完成任务%' 
            group by f_task_id
        ) r6 on t.f_id = r6.f_task_id
        left join (
            select B.f_task_id, B.f_id, B.f_type, B.f_create_time, TIMESTAMPDIFF(minute,A.f_create_time,B.f_create_time) / 60 timeDiff
            from (
                select a.*, (@i := @i + 1) as idx from (
                        select t.f_id f_task_id, f.f_id, t.f_type, t.f_create_time 
                        from t_factory f, t_task t 
                        where f.f_id = t.f_factoryIds and f.f_status = 0
                        and t.f_type in {_order_num} and t.f_create_time >= '{_startTime}' and t.f_create_time < '{_endTime}'
                        order by f.f_id, t.f_type, t.f_create_time
                ) as a , (select @i := 1) b
            ) A join (
                select a.*, (@j := @j + 1) as idx from (
                        select t.f_id f_task_id, f.f_id, t.f_type, t.f_create_time 
                        from t_factory f, t_task t 
                        where f.f_id = t.f_factoryIds and f.f_status = 0
                        and t.f_type in {_order_num} and t.f_create_time >= '{_startTime}' and t.f_create_time < '{_endTime}'
                        order by f.f_id, t.f_type, t.f_create_time
                ) as a , (select @j := 0) c
            ) B on A.idx = B.idx and A.f_id = B.f_id and A.f_type = B.f_type
        ) t1 on t.f_id = t1.f_task_id
        left join t_task_water_electricity e on e.f_task_id = t.f_id

        where 
            t.f_type in {_order_num}
            and t.f_create_time >= '{_startTime}'
            and t.f_create_time < '{_endTime}'
            and t.f_del_status != 1

        order by f.f_id,t.f_type,t.f_create_time
    """.format(_startTime=startTime, _endTime=endTime, _order_num=order_2)

    return pd_conn_zfw(sql2)


def work_order(path, startTime, endTime):

    title_time1 = startTime[-5:-3] + startTime[-2:]
    title_time2 = endTime[-5:-3] + endTime[-2:]
    title_time3 = datetime.strftime(datetime.now(), '%M%S')

    writer = pd.ExcelWriter(r'{}\运维工单明细{}~{}({}).xlsx'.format(path, title_time1, title_time2, title_time3))

    st = time.time()
    df1 = work_order_1(startTime, endTime)
    df1.to_excel(writer, index=False, sheet_name='其他')
    et = time.time()
    diff = et - st
    print('抢修/客需反馈、洁净问题/改造/整改/客诉/排查工单/C端差评工单 执行时间：' + str(diff) + 's')

    # st = time.time()
    # df2 = work_order_2(startTime, endTime)
    # df2.to_excel(writer, index=False, sheet_name='运维、保养')
    # et = time.time()
    # diff = et - st
    # print('运维、保养 执行时间：' + str(diff) + 's')

    writer.save()
    writer.close()


if __name__ == '__main__':
    path=''
    startTime=''
    endTime=''
    # work_order(path, startTime, endTime)